from datetime import datetime
import json
import pandas as pd
from execelUtil.checkData.checkPurProduceTransit import checkPurTrasitQtyWithSuplr
from execelUtil.excute_abstract import excute_occpy
from mysqlUtil.db_pool import execute_query, execute_update

def convert_to_json(filename):
    data_list = []

    # 读取 ODS 文件
    workbook = pd.read_excel(filename, sheet_name='Sheet1', engine='odf')
    df = pd.DataFrame(workbook, dtype='object')
    cc = df.where(pd.notna(workbook), None)

    # 遍历每一行数据
    for row in cc.values:
        if row[0] is None:
            break

        # 调用checkPurTrasitQtyWithSuplr获取供应商信息
        sku_code = row[10] if row[10] is not None else ""
        if sku_code:
            # 使用连接池执行数据库查询
            sql = "SELECT * FROM your_table WHERE sku_code = %s"
            db_result = execute_query(sql, (sku_code,))

            zpo_list, _ = checkPurTrasitQtyWithSuplr(sku_code)
            # 查找匹配的ZPO记录
            supplier = ""
            for zpo_info in zpo_list:
                if zpo_info["pur_zpo"] == row[3]:
                    supplier = zpo_info["suplr_code"]
                    break

        json_data = {
            "billTime": datetime.now().strftime("%Y-%m-%dT%H:%M:%S.%f")[:-3],
            "billTypeEnum": "SUPPLY_STRAIGHT_CANCEL",
            "businessBillNo": row[4] if row[4] is not None else "",
            "businessTypeEnum": "BAD_MANAGE",
            "operationTypeEnum": "SUPPLY_STRAIGHT_HOLD",
            "repairOpt": True,
            "skuList": [
                {
                    "oldSkuCode": row[11] if row[11] is not None else "",
                    "platform": row[8] if row[8] is not None else "",
                    "qty": int(row[12]) if row[12] is not None else 0,
                    "skuCode": sku_code,
                    "store": row[9] if row[9] is not None else "",
                    "supplier": supplier
                }
            ],
            "warehouseCode": '0' + str(row[7]) if row[7] < 10 else str(row[7])
        }
        data_list.append(json_data)

    return data_list

if __name__ == "__main__":
    # 使用示例
    filename = "/home/project_python/inventory-small-tool-backend/extFile/tmp-occoupy.ods"
    result = convert_to_json(filename)
    print(json.dumps(result, indent=2, ensure_ascii=False))
